/*================================================================
 *   Copyright (C) 2021 Sangfor Ltd. All rights reserved.
 *   
 *   文件名称：database.hpp
 *   创 建 者：wudu
 *   创建日期：2021年08月29日
 *   描    述：
 *
 ================================================================*/

#pragma once
#include <stdio.h>
#include <unistd.h>
#include <iostream>
#include <string>

#include <mysql/mysql.h>
#include <jsoncpp/json/json.h>
#include "httplib.h"

using namespace std;
using namespace httplib;

class DataBaseSvr
{
    public:
        DataBaseSvr(const string& db_host, const string& db_user, const string& db_password, const string& db_name, unsigned int db_port = 3306)
        {
            mysql_init(&mysql_);
            db_host_ = db_host;
            db_user_ = db_user;
            db_password_ = db_password;
            db_name_ = db_name;
            db_port_ = db_port;
        }

        ~DataBaseSvr()
        {

        }

        int ConnectToMysql()
        {
            if(!mysql_real_connect(&mysql_, db_host_.c_str(), db_user_.c_str(), db_password_.c_str(), db_name_.c_str(), db_port_, NULL, CLIENT_FOUND_ROWS))
            {
                cout << mysql_error(&mysql_) << endl;
                return -1;
            }
            return 0;
        }


        bool InsertUserInfo(const Json::Value& v)
        {
            //1.组织sql语句
            //{"name":"xxx","passwd":"xxx","email":"xxxxx","phonenum":"xxxxxxx"}
            string name = v["name"].asString();
            string passwd = v["passwd"].asString();
            string email = v["email"].asString();
            string phonenum = v["phonenum"].asString();
#define INSERT_USER_INFO "insert into sys_user(user_name, password, email, phone_num) values('%s', '%s', '%s', '%s');"
            char sql[1024] = {0};
            snprintf(sql, sizeof(sql) - 1, INSERT_USER_INFO, name.c_str(), passwd.c_str(), email.c_str(), phonenum.c_str());
            //cout << "sql:" << sql << endl;
            //2.继续执行sql
            //3.返回插入结果给调用这
            return ExecuteSql(sql);
        }


        bool ExecuteSql(const string& sql)
        {
            //1.设置mysql客户端的字符集为utf8
            mysql_query(&mysql_, "set names utf8");
            //2.执行sql
            //int     STDCALL mysql_query(MYSQL *mysql, const char *q);
            if(mysql_query(&mysql_, sql.c_str()) != 0)
            {
                cout << mysql_error(&mysql_) << endl;
                return false;
            }
            return true;
        }


        //res, 本质上是一个出参， 接收一个1级指针的地址
        bool ExecuteSql(const string& sql, MYSQL_RES** res)
        {
            //1.设置mysql客户端的字符集为utf8
            mysql_query(&mysql_, "set names utf8");
            //2.执行sql
            //int     STDCALL mysql_query(MYSQL *mysql, const char *q);
            if(mysql_query(&mysql_, sql.c_str()) != 0)
            {
                cout << mysql_error(&mysql_) << endl;
                return false;
            }

            //3.将mysql执行的结果放到结果集当中
            //MYSQL_RES *     STDCALL mysql_store_result(MYSQL *mysql); 
            *res = mysql_store_result(&mysql_);
            if(res == NULL)
            {
                return false;
            }
            return true;
        }

        int QueryUserExist(const Json::Value& v)
        {
            //1.从json对象当中解析出来 邮箱和密码
            string email = v["email"].asString();
            string password = v["password"].asString();
            //2.使用邮箱作为查询条件在sys_user表当中进行查询（如果没有查找到用户，则返回）
#define QUERY_USER "select * from sys_user where email='%s';"
            char sql[1024] = {0};
            snprintf(sql, sizeof(sql) - 1, QUERY_USER, email.c_str());
            cout << sql << endl;
            //3.针对查询的结果当中的密码， 进行比对
            MYSQL_RES* res = NULL;
            if(ExecuteSql(sql, &res) == false)
            {
                return -2;
            }

            //3.1 针对结果集进行操作， 判断下，结果集当中的行数是否等于1
            //    等于1， 继续往下执行
            //    不等1 ， 直接返回
            //my_ulonglong STDCALL mysql_num_rows(MYSQL_RES *res);
            if(mysql_num_rows(res) != 1)
            {
                cout << "No Data for sql!, sql is " << sql <<endl;
                return -3;
            }

            //3.2 在结果集当中获取一行数据
            //MYSQL_ROW   STDCALL mysql_fetch_row(MYSQL_RES *result);
            //     如果重复调用msyql_fetch_row这个函数， 默认是取下一行的数据
            MYSQL_ROW row = mysql_fetch_row(res);

            //3.3 比对密码
            string db_password = row[2];
            if(db_password != password )
            {
                return -4;
            }

            //4.返回查询结果
            return atoi(row[0]);
        }


        int GetMusic(Json::Value& resp_json)
        {
            const char* sql = "select * from music";
            MYSQL_RES* res = NULL;
            if(ExecuteSql(sql, &res) == false)
            {
                return -2;
            }

            int row_num = mysql_num_rows(res);
            if(row_num <= 0)
            {
                printf("No Data: sql is %s\n", sql);
                mysql_free_result(res); 
                return -3;
            }

            MYSQL_ROW row = mysql_fetch_row(res);
            Json::Value music_value;
            while(row != NULL)
            {
                Json::Value tmp;
                tmp["id"] = atoi(row[0]);
                tmp["title"] = (row[1]);
                tmp["singer"] = (row[2]);
                tmp["url"] =  (row[3]);
                music_value.append(tmp);
                row = mysql_fetch_row(res);
            }
            resp_json["music"] = music_value;
            mysql_free_result(res); 
            return 0; 
        }

        bool InsertLoveMusic(const Request& req, int user_id)
        {
            string music_id = req.body;
#define INSERT_LOVE_MUSIC "INSERT INTO love_music(user_id, music_id) VALUES(%d, %d);"
            char sql[1024] = {0};
            snprintf(sql, sizeof(sql) - 1, INSERT_LOVE_MUSIC, user_id, atoi(music_id.c_str()));
            return ExecuteSql(sql);
        }
    private:
        MYSQL mysql_;

        string db_host_;
        string db_user_;
        string db_password_;
        string db_name_;
        unsigned int db_port_;
};
